const express = require('express')
const db = require('../libs/DBhelper')
const md5 = require('md5') //md5加密
const jwt = require('jwt-simple') //制作token

const abcRouter = express.Router()

//检测邮箱是否存在
abcRouter.get('/checkemail', async function (req, res) {
  let sql = `select * from users where email = ?`
  let params = [req.query.email]
  let result = await db.exec(sql, params)
  if (result && result.length > 0) {
    res.send({ code: 0, msg: '邮箱账号已存在' })
  } else {
    res.send({ code: 1, msg: '邮箱账号可用' })
  }
})

//登录接口
abcRouter.post('/login', async function (request, response, next) {
  let sql =
    'select `email`  from `users` where `email`=? and `pwd` =? and `status`=1 and power = 0'
  let params = [request.body.email, md5(request.body.pwd)]
  const result = await db.exec(sql, params)
  const status = result && result.length >= 1
  if (status) {
    const token = jwt.encode(result[0], 'hongs', 'HS256')
    response.json({ msg: '登录成功', code: 1, data: result[0], token })
  } else {
    response.json({ msg: '登录失败', code: -1 })
  }
})

//注册接口
abcRouter.post('/reg', async function (request, response, next) {
  let time = Date.now()
  let sql = 'insert into `users` (`email`,`pwd`,`datatime`) values(?,?,?);'
  if (!request.body.email || !request.body.pwd) {
    return response.json({ msg: '注册失败请求参数不对劲', code: -1 })
  }
  let params = [request.body.email, md5(request.body.pwd), time]
  try {
    let result = await db.exec(sql, params)
    var status = result && result.affectedRows >= 1
    response.json({
      msg: status ? '注册成功' : '注册失败',
      code: Number(status),
    })
  } catch (error) {
    response.json({ msg: '注册失败', code: -1, error })
  }
})


//获取轮播图
abcRouter.get('/getbanner', async function (request, response, next) {
  let sql = 'select * from `banner`'
  let result = await db.exec(sql)
  if (result && result.length > 0) {
    response.send({ code: 1, msg: '获取轮播图成功', data: result })
  } else {
    response.send({ code: 0, msg: '获取轮播图失败' })
  }
})

//获取博客数据
abcRouter.get('/getblog', async function (request, response, next) {
  let sql = 'select * from `blogs`'
  let result = await db.exec(sql)
  if (result && result.length > 0) {
    response.send({ code: 1, msg: '获取博客数据成功', data: result })
  } else {
    response.send({ code: 0, msg: '获取博客数据失败' })
  }
})

//根据id获取博客内容
abcRouter.get('/getblogbyid', async function (request, response, next) {
  let sql = `select * from blogs where id =?`
  let params = [request.query.id]
  let result = await db.exec(sql, params)
  if (result && result.length > 0) {
    response.send({ code: 1, msg: '获取博客成功', data: result })
  } else {
    response.send({ code: 0, msg: '获取博客失败' })
  }
})

//获取博客评论数据
abcRouter.get('/getcomment', async function (request, response, next) {
  let sql = 'select * from `comment`'
  let result = await db.exec(sql)
  if (result && result.length > 0) {
    response.send({ code: 1, msg: '获取评论数据成功', data: result.reverse() })
  } else {
    response.send({ code: 0, msg: '获取评论数据失败' })
  }
})

//发表博客评论
abcRouter.post('/addcomment', async function (request, response, next) {
  let sql = 'insert into `comment` (`email`,`content`,`datetime`) values(?,?,?);'
  let params = [request.body.email, request.body.content, Date.now()]
  try {
    let result = await db.exec(sql, params)
    var status = result && result.affectedRows >= 1
    response.json({
      msg: status ? '发表评论成功' : '发表评论失败',
      code: Number(status),
    })
  } catch (error) {
    response.json({ msg: '发表评论失败', code: -1, error })
  }
})

//获取商品分类
abcRouter.get('/getgoodscategory', async function (request, response, next) {
  let sql = 'select `gname`,`grade`,`gid`,`icon` from goodscategory where grade = 1'
  let result = await db.exec(sql)
  if (result && result.length > 0) {
    for (let i = 0; i < result.length; i++) {
      result[i].children = await db.exec(
        `select * from goodscategory where gid =${i + 1} and grade != 1 `,
      )
    }
    response.send({ code: 1, msg: '获取商品分类成功', data: result })
  } else {
    response.send({ code: 0, msg: '获取商品分类失败' })
  }
})

// 根据品牌bid获取商品
abcRouter.get('/getgoodsbybid', async function (request, response, next) {
  let sql = `select * from goods where bid =?`
  let params = [request.query.bid]
  let result = await db.exec(sql, params)
  if (result && result.length > 0) {
    response.send({ code: 1, msg: '获取商品成功', data: result })
  } else {
    response.send({ code: 0, msg: '获取商品失败' })
  }
})

// 根据分类gid获取商品
abcRouter.get('/getgoodsbygid', async function (request, response, next) {
  let sql = `select * from goods where gid =?`
  let params = [request.query.gid]
  let result = await db.exec(sql, params)

  if (result && result.length > 0) {
    for (let i = 0; i < result.length; i++) {
      result[i].img = result[i].img.split('$')[0].trim()
    }
    response.send({ code: 1, msg: '获取商品成功', data: result })
  } else {
    response.send({ code: 0, msg: '获取商品失败' })
  }
})

//根据内容模糊查询商品
abcRouter.get('/getgoodsbycontent', async function (request, response, next) {
  let a=''
  if(request.query.gid){
     a = [`gid = ${request.query.gid} and`]
  }
  let sql = `select * from goods where ${a} ( name like '%${request.query.content}%'  or 'desc' like '%${request.query.content}%' or brand like '%${request.query.content}%' )`
  let result = await db.exec(sql)
  if (result && result.length > 0) {
    for (let i = 0; i < result.length; i++) {
      result[i].img = result[i].img.split('$')[0].trim()
    }
    response.send({ code: 1, msg: '查询商品成功', data: result })
  } else {
    response.send({ code: 0, msg: '查询失败或暂无商品' })
  }
})

//根据商品的价格区间查询商品
abcRouter.get('/getgoodsbyprice', async function (request, response, next) {
  if (parseInt(request.query.min) > parseInt(request.query.max)) {
    return response.send({ code: 0, msg: '最低价格不能大于最高价格' })
  }
  if (
     request.query.min == 'undefined' ||
     request.query.max == 'undefined'
  ) {
    return response.send({ code: 0, msg: '最低价格或最高价格不能为空' })
  }
  if (
    typeof parseInt(request.query.min) !== 'number' ||
    typeof parseInt(request.query.max) !== 'number'
  ) {
    return response.send({ code: 0, msg: '最低价格或最高价格不是数字' })
  }
  let sql = `select * from goods where price between ${request.query.min} and ${request.query.max}`
  let result = await db.exec(sql)
  if (result && result.length > 0) {
    for (let i = 0; i < result.length; i++) {
      result[i].img = result[i].img.split('$')[0].trim()
    }
    response.send({ code: 1, msg: '查询商品成功', data: result })
  } else {
    response.send({ code: 0, msg: '查询失败或暂无商品' })
  }
})

//获取商品评论page=1&limit=10
abcRouter.get('/getpubliccomment', async function (request, response, next) {
  let page = request.query.page
  let limit = request.query.limit
  if (page && limit) {
    page = parseInt(page)
    limit = parseInt(limit)
  } else {
    page = 1,
      limit = 100
  }
  let params = [(page - 1) * limit, limit]
  let sql = 'SELECT * FROM `pubilccomment` LIMIT ?,?'
  let result = await db.exec(sql, params)
  if (result && result.length > 0) {
    response.send({ code: 1, count: result.length, msg: '获取评论成功', data: result })
  } else {
    response.send({ code: 0, msg: '获取评论失败' })
  }
})

//发表评论
abcRouter.post('/addpubliccomment', async function (request, response, next) {
  let sql =
    'insert into pubilccomment(name,content,datetime) values(?,?,?)'
  if (!request.body.email || !request.body.content) {
    return response.send({ code: 0, msg: '参数错误' })
  }
  let params = [request.body.email, request.body.content, Date.now()]
  let result = await db.exec(sql, params)
  if (result.affectedRows > 0) {
    response.send({ code: 1, msg: '发表评论成功' })
  } else {
    response.send({ code: 0, msg: '发表评论失败' })
  }
})

//根据商品id获取商品详情
abcRouter.get('/getgoodsbyid', async function (request, response, next) {
  let sql = `select * from goods where id =?`
  let params = [request.query.id]
  let result = await db.exec(sql, params)
  if (result && result.length > 0) {
    result[0].img = [...result[0].img.split('$')]
    response.send({ code: 1, msg: '获取商品成功', data: result })
  } else {
    response.send({ code: 0, msg: '获取商品失败' })
  }
})

//获取全部商品
abcRouter.get('/getgoodsbyrandom', async function (request, response, next) {
  let sql = `select * from goods order by rand() limit 11`
  let result = await db.exec(sql)
  if (result && result.length > 0) {
    for (let i = 0; i < result.length; i++) {
      result[i].img = result[i].img.split('$')[0].trim()
    }
    response.send({ code: 1, msg: '获取商品成功', data: result })
  } else {
    response.send({ code: 0, msg: '获取商品失败' })
  }
})


//根据email查询历史订单
abcRouter.get('/getorderbyemail', async function (request, response, next) {
  let sql = `select * from orders where email =?`
  if (!request.query.email) return response.send({ code: 0, msg: '参数错误' })
  let params = [request.query.email]
  let result = await db.exec(sql, params)
  if (result && result.length > 0) {
    response.send({ code: 1, msg: '获取订单成功', data: result })
  } else {
    response.send({ code: 0, msg: '暂无订单' })
  }
})
module.exports = abcRouter
